﻿//------------------------------------------------------------
// All Rights Reserved , Copyright (C) 2010 , Jirisoft , Ltd. 
//------------------------------------------------------------

using System;
using System.Data;
using System.Reflection;
using System.Web;
using Microsoft.Office.Interop.Excel;
using ExcelApplication = Microsoft.Office.Interop.Excel.Application;

namespace HUA.CommonUtilities
{
    /// <summary>
    /// BaseExportExcel
    /// 导出Excel格式数据
    /// 
    /// 修改纪录
    /// 
    ///     2009.07.08 版本：2.0 JiRiGaLa	更新完善程序，将方法修改为静态方法。
    ///     2006.12.02 版本：1.0 JiRiGaLa	新创建。
    /// 
    /// 版本：2.0
    ///
    /// <author>
    ///		<name>JiRiGaLa</name>
    ///		<date>2009.07.08</date>
    /// </author> 
    /// </summary>
    public class BaseExportExcel
    {
        #region private void DeleteExistFile(string fileName) 删除已经存在的文件
        /// <summary>
        /// 删除已经存在的文件
        /// </summary>
        /// <param name="fileName">文件路径</param>
        private void DeleteExistFile(string fileName)
        {
            if (System.IO.File.Exists(fileName))
            {
                System.IO.File.Delete(fileName);
            }
        }
        #endregion

        #region public void GetExcelFile(DataTable dataTable, string templateFilePath, string outFilePath, int startRowIndex, int startColIndex, string downloadPath) 导出Excel
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="dataTable">数据表</param>
        /// <param name="paramOutFileName">输出名称</param>
        /// <param name="outFilePath">输出路径</param>
        /// <param name="startRowIndex">粘贴起始行</param>
        /// <param name="startColIndex">粘贴起始列</param>
        /// <param name="downloadPath">下载路径</param>
        public void GetExcelFile(System.Data.DataTable dataTable, string templateFilePath, string outFilePath, int startRowIndex, int startColIndex, string downloadPath)
        {
            int colIndex = 0;
            int rowIndex = 0;
            int rowCount = dataTable.Rows.Count;
            int colCount = dataTable.Columns.Count + 1;
            DataView dataView = dataTable.DefaultView;
            templateFilePath = HttpContext.Current.Server.MapPath(templateFilePath);
            string outTemplateFilePath = HttpContext.Current.Server.MapPath(outFilePath);
            ExcelApplication excel = new ExcelApplication();
            excel.Visible = false;
            // 检测模版是否存在
            if (System.IO.File.Exists(templateFilePath))
            {
                excel.Workbooks.Open(templateFilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            }
            else
            {
                excel.Workbooks.Add(Missing.Value);
            }
            Worksheet worksheet = (Worksheet)excel.ActiveSheet;
            Object[,] objData = new Object[rowCount, colCount];

            foreach (DataRowView Row in dataView)
            {
                colIndex = 0;
                foreach (DataColumn Column in dataView.Table.Columns)
                {
                    if (colIndex == 0)
                    {
                        objData[rowIndex, colIndex] = rowIndex + 1;
                        colIndex++;
                    }

                    if (Column.DataType == System.Type.GetType("System.DateTime"))
                    {
                        if (Row[Column.ColumnName].ToString().Length > 0)
                        {
                            objData[rowIndex, colIndex] = (Convert.ToDateTime(Row[Column.ColumnName].ToString()).ToString());
                        }
                        else
                        {
                            objData[rowIndex, colIndex] = string.Empty;
                        }
                    }
                    else
                    {
                        if (Column.DataType == System.Type.GetType("System.String"))
                        {
                            objData[rowIndex, colIndex] = "'" + Row[Column.ColumnName].ToString();
                        }
                        else
                        {
                            objData[rowIndex, colIndex] = Row[Column.ColumnName].ToString();
                        }
                    }
                    colIndex ++;
                }
                rowIndex++;
            }
            // 写入Excel
            Range range = worksheet.get_Range(worksheet.Cells[startRowIndex, startColIndex], worksheet.Cells[rowCount + startRowIndex -1, colCount + startColIndex -1]);
            range.Value2 = objData;
            // 删除已经存在的文件
            this.DeleteExistFile(outTemplateFilePath);
            worksheet.SaveAs(outTemplateFilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            excel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            GC.Collect();
            HttpContext.Current.Response.Redirect(downloadPath);
        }
        #endregion
    }
}